
package com.scalar.chiptrack.marketing.dao;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.marketing.Pricing;

import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.calendar.Period;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;

/**
 * Implementation of <strong>DAO</strong> business logic for
 * search with sales order
 *
 * @author Kesava Kumar Achanala
 * @version $Revision: 1.3 $ $Date: 2007/07/31 05:52:31 $
 */



public class PricingDAO
{
   /**
    * Default Constructor
    *
    */
   public PricingDAO()
   {

   }


   // --------------------------------------------------------- Public Methods

   /**
    * Get the Pricing Details for Standard Pricing
    *
    * @param productId
    * @return Pricing
    */
   public static ArrayList getStandardPricing(int productId, int isSample) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList priceList = new ArrayList();
        
        try 
        {
          conn = DataSource.getConnection();
           
          pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.selectStandardPricing"));
          pstmt.setInt (1, productId );
          pstmt.setInt (2, isSample );
            
          rs = pstmt.executeQuery();
          Pricing pricing = null;
           
          while(rs.next()) 
          {
        		pricing = new Pricing(
                            rs.getInt("Product_Id"),
                            rs.getInt("PPT_Qty_From")+"",
                            rs.getInt("PPT_Qty_To")+"",
                            rs.getDouble("PPT_Unit_Price"),
                            rs.getInt("is_sample"));

                
                //System.out.println("form qty "+ pricing.getFromQty()+ " to qty " + pricing.getToQty() );            
                            
            	if( ( pricing.getFromQty() != null || pricing.getToQty() != null ) && (!pricing.getFromQty().equals("1") || !pricing.getToQty().equals("1"))  && (!pricing.getFromQty().equals("0") || !pricing.getToQty().equals("0")) )
            	{
					priceList.add(pricing);  
            	}
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Stnd Pricing Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return priceList;
                     
   }

   /**
    * Get the Pricing Details for Customer basis
    *
    * @param productId
    * @return Pricing
    */
   public static ArrayList getCustomerPricing(int productId, int customerId, int sample) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList priceList = new ArrayList();
                
        try {
           conn = DataSource.getConnection();
           
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.selectCustomerPricing"));
           pstmt.setInt (1, productId);
           pstmt.setInt (2, customerId);
           pstmt.setInt (3, sample);
           rs = pstmt.executeQuery();
           Pricing pricing = null;

           while(rs.next()) {
        	pricing = new Pricing(
                            rs.getInt("Product_Id"),
                            rs.getInt("Customer_Id"),
                            rs.getInt("CPT_Qty_From")+"",
                            rs.getInt("CPT_Qty_To")+"",
                            rs.getDouble("CPT_Unit_Price"),
                            rs.getInt("is_sample"));

                priceList.add(pricing);
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customer Pricing Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return priceList;
                     
   }
   // Added by Veeru on 1st August 2004
   public static HashMap getQuarterPricing(int productId, int sample) throws ScalarException
  {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       Period period = null;

       ArrayList tempPeriodList = null;
       ArrayList periodList = null;
       ArrayList priceList = null;
       int yearID = 0;
       int listSize = 0;
       String newList = "TRUE";
       HashMap hashMap = null;

       try {
                conn = DataSource.getConnection();

                pstmt = conn.prepareStatement(SqlQueryMappings.get("calendar.loadCompanyCalendar"));
                pstmt.setInt(1, (java.util.Calendar.getInstance()).get(java.util.Calendar.YEAR ));
                rs = pstmt.executeQuery();
                while (rs.next()) {
                        yearID = rs.getInt("CC_YearID");
                }
                pstmt.close();
                pstmt = null;
                tempPeriodList = new ArrayList();
                pstmt = conn.prepareStatement(SqlQueryMappings.get("calendar.loadQuarters"));
                pstmt.setInt(1, yearID );
                rs = pstmt.executeQuery();
                while(rs.next()) {
                        period = new Period();
                        period.setQuarterId(rs.getInt("CQC_Quarter_ID")) ;
                        period.setQuarter(rs.getString("CQC_Quarter"));
                        period.setStartDate(StringUtils.formatDateToString( rs.getDate("CQC_Start_Date"), null) );
                        period.setEndDate(StringUtils.formatDateToString( rs.getDate("CQC_End_Date"), null) );
                        tempPeriodList.add(period);
                }
                periodList = new ArrayList();
                for ( int i = 0 ; i < tempPeriodList.size(); i++ )
                {
                    priceList = new ArrayList();
                    period = (Period)tempPeriodList.get(i);
                    pstmt.close();
                    pstmt = null;
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.selectQuarterPricingWithQuarterId"));
                    pstmt.setInt (1, productId);
                    pstmt.setInt (2, period.getQuarterId());
                    pstmt.setInt (3, sample);

                    rs = pstmt.executeQuery();
                    Pricing pricing = null;

                    while(rs.next()) {
                        pricing = new Pricing(
                                   rs.getInt("Product_Id"),
                                   rs.getInt("Quarter_Id"),
                                   rs.getInt("QPT_Qty_From")+"",
                                   rs.getInt("QPT_Qty_To")+"",
                                   rs.getDouble("QPT_Unit_Price"),
                                   rs.getInt("is_sample")
                        );
                        priceList.add(pricing);
                    }
                    listSize = priceList.size();
                    if ( listSize < 4 )
                    {
                        for( int j = 0; j < ( 4 - listSize ); j++ ) {
                            priceList.add( new Pricing() );
                        }
                    }
                    if ( listSize > 0 )
                    {
                        newList = "FALSE";
                    }
                    period.setPriceList(priceList);
                    periodList.add(period);
                }
                hashMap = new HashMap();
                hashMap.put("list",periodList);
                hashMap.put("newList", newList);
       }
       catch (Exception sqlex) {
           sqlex.printStackTrace();
           throw new ScalarException ("Could not get Quarter Pricing Info from database", sqlex);
       }
       finally {
           if (rs != null) {
               try { rs.close(); } catch (SQLException excp) {}
           }
           if (pstmt != null) {
               try { pstmt.close(); } catch (SQLException excp) {}
           }
           if (conn != null) {
               try { conn.close(); } catch (SQLException excp) {}
           }
       }
       return hashMap;
  }



   /**
    * Save the Standard Pricing Details
    *
    * @param productId, fromQty, toQty, Price
    */
    public static void saveStandardPricing(DBTransaction dbTransaction, int productId, 
           String[] fromQty, String[] toQty, String[] price, User user, int sample) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {

            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.selectStandardPricing"));
            pstmt.setInt(1, productId);
            pstmt.setInt(2, sample);
            rs = pstmt.executeQuery();

            if(rs.next() == false) {
                //Nothing to do...
            } else
            {
                if(pstmt != null)  {
                    pstmt.close();
                    pstmt = null;
                }
                cstmt = conn.prepareCall("{Call update_stnd_price(?,?)}");
                cstmt.setInt (1, productId);
                cstmt.setInt (2, sample);
                cstmt.execute();
            }

            //Close the previous connection to avoid cloned connection exception
            if(pstmt != null) {
                pstmt.close();
                pstmt = null;
            }

            if(cstmt != null) {
                cstmt.close();
                cstmt = null;
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.insertStndPricing"));
            //Loop thru the array of elements and save it on database
            for(int i=0; i<fromQty.length; i++)
            {
                 if(price[i].equals("")) price[i] = "0.0";

                 if((!fromQty[i].equals("")) && (!toQty[i].equals(""))) {
                    pstmt.setInt(1, productId);
                    pstmt.setInt(2, Integer.parseInt(fromQty[i]));
                    pstmt.setInt(3, Integer.parseInt(toQty[i]));
                    // Modified by Prabhanjan (10 May 2004)
                    //pstmt.setFloat(4, Float.parseFloat(price[i]));
                    pstmt.setDouble(4, Double.parseDouble((price[i])));
                    pstmt.setString(5, user.getUserId());
                    pstmt.setInt(6, sample);
                    pstmt.addBatch();
                 }

            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Standard Pricing into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
        }
   }
 

   /**
    * Save the Customer Pricing Details
    *
    * @param productId, customerId, fromQty, toQty, Price
    */
    public static void saveCustomerPricing(DBTransaction dbTransaction, int customerId,
                int productId, String[] fromQty, String[] toQty, String[] price, User user, int isSample)
                    throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;        
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;
                
        try {
            
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.selectCustomerPricing"));
            pstmt.setInt(1, productId);
            pstmt.setInt(2, customerId);
            pstmt.setInt(3, isSample);
            rs = pstmt.executeQuery();
            
            if(rs.next() == false) {
                //Nothing to do...
            } else {
                if(pstmt != null)  {
                    pstmt.close(); 
                    pstmt = null; 
                }
                cstmt = conn.prepareCall("{Call update_customer_price(?,?,?)}");
                cstmt.setInt (1, productId);
                cstmt.setInt (2, customerId);
                cstmt.setInt (3, isSample);
                cstmt.execute();
            }
            
            //Close the previous connection to avoid cloned connection exception
            if(pstmt != null) {
                pstmt.close(); 
                pstmt = null; 
            }
            
            if(cstmt != null) {
                cstmt.close();
                cstmt = null;
            }
            
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.insertCustomerPricing"));
            //Loop thru the array of elements and save it on database
            for(int i=0; i<fromQty.length; i++)
            {
                 if(price[i].equals("")) price[i] = "0.0";
                 
                 if((!fromQty[i].equals("")) && (!toQty[i].equals(""))) {                     
                    pstmt.setInt(1,customerId);
                    pstmt.setInt(2, productId);
                    pstmt.setInt(3, Integer.parseInt(fromQty[i]));
                    pstmt.setInt(4, Integer.parseInt(toQty[i]));
                    // Modified by Prabhanjan (10 May 2004)
                    //pstmt.setFloat(5, Float.parseFloat(price[i]));
                    pstmt.setDouble(5, Double.parseDouble((price[i])));
                    pstmt.setString(6, user.getUserId());
                    pstmt.setInt(7, isSample);
                    
                    pstmt.addBatch();
                 }             
                 
            }
            
            pstmt.executeBatch();
                 
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Standard Pricing into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) 
                            conn.commit();
                        else 
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
        }
    }
    // Added by veeru on 1st August 2004
    public static void saveQuarterPricing(DBTransaction dbTransaction, int productId,ArrayList periodList ,User user, int sample)
                    throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;

        Period period = null;
        Pricing pricing = null;
        ArrayList priceList = null;

        try {

            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.selectQuarterPricing"));
            pstmt.setInt(1, productId);
            pstmt.setInt(2, sample);
            rs = pstmt.executeQuery();

            if(rs.next() == false) {
                //Nothing to do...
            } else {
                if(pstmt != null)  {
                    pstmt.close();
                    pstmt = null;
                }
                cstmt = conn.prepareCall("{Call update_quarter_price(?, ?)}");
                cstmt.setInt (1, productId);
                cstmt.setInt (2, sample);
                cstmt.execute();
            }

            if(pstmt != null) {
                pstmt.close();
                pstmt = null;
            }

            if(cstmt != null) {
                cstmt.close();
                cstmt = null;
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.insertQuarterPricing"));

            //Loop thru the array of elements and save it on database
            for(int i = 0; i < periodList.size(); i++)
            {
                 period = (Period)periodList.get( i );
                 priceList = period.getPriceList();
                 for( int j = 0; j < priceList.size(); j++)
                 {
                     pricing = ( Pricing )priceList.get( j );

                     if( ( !"".equals(pricing.getFromQty()) && ( !"".equals(pricing.getToQty()) ) ) ) {
                        pstmt.setInt(1, period.getQuarterId());
                        pstmt.setInt(2, productId);
                        pstmt.setInt(3, Integer.parseInt(pricing.getFromQty()));
                        pstmt.setInt(4, Integer.parseInt(pricing.getToQty()));
                        pstmt.setDouble(5, pricing.getPrice());
                        pstmt.setString(6, user.getUserId());
                        pstmt.setInt(7, sample);
                        pstmt.addBatch();
                     }
                 }

            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Quarter Pricing into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
        }
    }

  /**
    * Get the ProductId for this customer
    *
    * @param marketingPartNo
    * @return PrpductId
    */
   public static int getProductId(int marketingPartNo) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int productId = -1;
        
        try {
           conn = DataSource.getConnection();
           
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.getProductId"));
           pstmt.setInt (1, marketingPartNo);
            
           rs = pstmt.executeQuery();
                      
           if(rs.next() == false) {
        	//Not able to get marketing part number
           } else {
               productId = rs.getInt("Product_ID");
           }
           
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get product Id from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return productId;
   }

    public static void saveAllPricingDetails(DBTransaction dbTransaction, ArrayList priceRangeList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareCall( "{ call sp_save_price_setup_details ( ?, ?, ?, ?, ?, ?, ?, ?) }" );

            Iterator priceIterator = priceRangeList.iterator();
            Pricing priceRange     = null;
            while ( priceIterator.hasNext() )
            {
               try
               {
                    priceRange = (Pricing) priceIterator.next();

                    pstmt.setString(1, priceRange.getMktPartNo() );
                    pstmt.setInt(2, priceRange.getQuater() );
                    pstmt.setInt(3, priceRange.getYear() );
                    double[][] samplepriceRange = priceRange.getSamplePriceRange();
                    double[][] regularpriceRange = priceRange.getRegularPriceRange();
                   
                    for ( int i=0; i< regularpriceRange.length; i++ )
                    {
                        pstmt.setInt(4,  (int)regularpriceRange[i][0]  );   //from
                        pstmt.setInt(5, (int)regularpriceRange[i][1] );   //to
                        pstmt.setDouble(6, regularpriceRange[i][2] );   //Regular price
                        pstmt.setDouble(7, samplepriceRange [i][2] );   //Sample price
                        pstmt.setInt(8, i );                            //FLAG FOR DELETE 
                        pstmt.addBatch();
                    }
                    pstmt.executeBatch();
                    pstmt.clearBatch();
                    success = true;
                }
                catch (SQLException sqle)
                {
                    success = false;
                    System.out.println("\n\n\nCould not update Standard Pricing into database" + sqle + "---\n\n\n" );
                }
            }
        }
        catch (Exception sqle)
        {
           success = false;
           throw new ScalarException ("Could not update Standard Pricing into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
   }
}